package com.roc.service.impl.change32and43;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jboss.logging.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.dao.DaoException;
import com.roc.dao.IBaseDao;
import com.roc.dao.ISQLBaseDao;
import com.roc.dao.merinfo.MerinfoChangeCheckDao;
import com.roc.newPage.IPageDao;
import com.roc.pojo.Operators;
import com.roc.pojo.TblAreaInf;
import com.roc.service.ServiceException;
import com.roc.service.change32and43.Change32And43Service;
import com.roc.service.impl.ReviseMerinfoServiceImpl;
import com.roc.util.Constance;
@Service
@Transactional
public class Change32And43ServiceImpl implements Change32And43Service{

	@Autowired
	private IBaseDao baseDao;
	@Autowired
	private IPageDao pageDao;
	
	@Autowired
	private ISQLBaseDao sqlBaseDao;
	
	@Autowired
	private MerinfoChangeCheckDao merinfoChangeCheckDao;
	
	static Logger logger=Logger.getLogger(ReviseMerinfoServiceImpl.class);
	@Override
	public Map<String, Object> importChange32List(File file, Operators op)throws ServiceException{
		//上传文件错误原因及定位
		Map<String,Object> resultMap=new LinkedHashMap<String, Object>();
		//存放上传数据
		Map<Integer,List<Object>> uploadData=new LinkedHashMap<Integer, List<Object>>();
		logger.info("上传Excel文件流转换为Map集合");
		boolean uploadResult=transfromToExcel32(file, (LinkedHashMap<Integer, List<Object>>) uploadData,(LinkedHashMap<String, Object>) resultMap);
		if(!uploadResult)
		{
			return resultMap;//返回错误原因
		}
		//验证Excel表数据正确性
		logger.info("验证Excel表数据正确性");
		boolean correntValidate=verifyDataCorrectness32(uploadData, resultMap);
		if(!correntValidate)
		{
			return resultMap;//返回校验原因
		}
		//在商户表中查询32域
		int count=0;
		Iterator<Entry<Integer, List<Object>>> it=uploadData.entrySet().iterator();
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();
			
			/*if(StringUtils.isBlank(String.valueOf(list.get(0)))){
				resultMap.put("result","32更新失败！商户号不能为空!");
				return resultMap;
			}*/
			try {
				
				//在商户表中查询32域
				logger.info("在商户表中查询32域");
				List<Object> querylist=null;
				querylist = query32(list);
				//判断商户号是否存在
				if(querylist.isEmpty()){
					resultMap.put("error","失败！此商户号【"+list.get(0)+"】不存在！");
					continue;
				}
				//将更改信息写入到到新表
				logger.info("将更改信息写入到到新表");
				insert32Info(querylist,list,op);
				//更新商户表中的32域
				logger.info("更新商户表中的32域");
				updateMcht32(querylist,list);
				count++;
			} catch (DaoException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				logger.info("更32域失败");
				resultMap.put("result","由于某些未知的原因,程序崩溃了！请联系技术人员！/(ㄒoㄒ)/~~");
				return resultMap;
			}
		}
		resultMap.put("result","您已成功变更【"+count+"】条32域");				
		return resultMap;
	}
	@Override
	public Map<String, Object> importChange43List(File file,Operators op) throws ServiceException {
		//上传文件错误原因及定位
			Map<String,Object> resultMap=new LinkedHashMap<String, Object>();
			//存放上传数据
			Map<Integer,List<Object>> uploadData=new LinkedHashMap<Integer, List<Object>>();
			logger.info("上传Excel文件流转换为Map集合");
			boolean uploadResult=transfromToExcel43(file, (LinkedHashMap<Integer, List<Object>>) uploadData,(LinkedHashMap<String, Object>) resultMap);
			if(!uploadResult)
			{
				return resultMap;//返回错误原因
			}
			//验证Excel表数据正确性
			logger.info("验证Excel表数据正确性");
			boolean correntValidate=verifyDataCorrectness43(uploadData, resultMap);
			if(!correntValidate)
			{
				return resultMap;//返回校验原因
			}
			//在商户表中查询43域
			int count=0;
			Iterator<Entry<Integer, List<Object>>> it=uploadData.entrySet().iterator();
			while(it.hasNext())
			{
				Entry<Integer, List<Object>> entry=it.next();
				List<Object> list=entry.getValue();
				
				/*if(StringUtils.isBlank(String.valueOf(list.get(0)))){
					resultMap.put("result","43更新失败！商户号不能为空!");
					return resultMap;
				}*/
				try {
					
					//在商户表中查询43域
					logger.info("在商户表中查询43域");
					List<Object> querylist=null;
					querylist = query43(list);
					if(querylist.isEmpty()){
						ArrayList<String> errorList=new ArrayList<String>();
						errorList.add("失败！此商户号【"+list.get(0)+"】不存在！");
						resultMap.put("error",errorList);
						continue;
					}
					//将更改信息写入到到新表
					logger.info("将更改43信息写入到到新表");
					insert43Info(querylist,list,op);
					//更新商户表中的32域
					logger.info("更新商户表中的43域");
					updateMcht43(querylist,list);
					count++;
				} catch (DaoException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					logger.info("更43域失败");
					resultMap.put("result","由于某些未知的原因,程序崩溃了！ 请联系技术人员！/(ㄒoㄒ)/~~");
					return resultMap;
				}
			}
			resultMap.put("result","您已成功变更【"+count+"】条43域");
		return resultMap;
	}

	/**
	 * 查询商户表里原有的32域
	 * @throws DaoException 
	 */
	
	public List<Object> query32(List<Object> list) throws DaoException{
		String querySql="select mcht_cd ,uc_bc_cd_32 from  tbl_mcht_inf where mcht_cd=?";
		Object[] param={list.get(0)};
		List<Object> querylist = pageDao.querySqlAll(querySql, param);
		return querylist;
	}
	
	
	/**
	 * 将更改32信息写入到新表中
	 * @throws DaoException 
	 */
	
	public void insert32Info(List<Object> querylist,List<Object> list,Operators op) throws DaoException{

		String mcht_cd=String.valueOf(loopList(querylist).get(0));
		String ucBcCd32=String.valueOf(loopList(querylist).get(1));
		
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM--dd HH:mm:ss");
		String date=sdf.format(new Date());
		TblAreaInf ta = new TblAreaInf();
		ta.setMchtCd(String.valueOf(mcht_cd));
		ta.setUcBcCd32(String.valueOf(ucBcCd32));
		ta.setUcBcCd32_his(String.valueOf(list.get(1)));
		ta.setOpId(String.valueOf(op.getId()));
		ta.setCreatedate(new Date());
		baseDao.editSave(ta);
	}
	
	
	/**
	 * 修改商户表中的32信息
	 * @throws DaoException 
	 */
	
	public void updateMcht32(List<Object> querylist,List<Object> list) throws DaoException{
		String mcht_cd=String.valueOf(loopList(querylist).get(0));
		String ucBcCd32=String.valueOf(loopList(querylist).get(1));
		
		/*SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
		String date=sdf.format(new Date());*/
		String updateSql="update tbl_mcht_inf t  set t.uc_bc_cd_32="+list.get(1)+" ,t.rec_upd_ts=? ,t.oper_in='U' where t.mcht_cd=? ";
		List<Object> parmaslist=new ArrayList<Object>();
		parmaslist.add(new Date());
		parmaslist.add(mcht_cd);
		sqlBaseDao.deleteBySql(updateSql, parmaslist);
		
	}
	

	/**
	 * 查询商户表里原有的43域
	 * @throws DaoException 
	 */
	
	public List<Object> query43(List<Object> list) throws DaoException{
		String querySql="select mcht_cd ,name_busi from  tbl_mcht_inf where mcht_cd=?";
		Object[] param={list.get(0)};
		List<Object> querylist = sqlBaseDao.queryAll(querySql,param);
		return querylist;
	}
	
	/**
	 * 修改商户表中的43信息
	 * @throws DaoException 
	 */
	
	public void updateMcht43(List<Object> querylist,List<Object> list) throws DaoException{
		String mcht_cd=String.valueOf(loopList(querylist).get(0));
		String name_busi=String.valueOf(loopList(querylist).get(1));
		
		String updateSql="update tbl_mcht_inf t  set t.name_busi= '"+list.get(1)+"' ,t.rec_upd_ts=? ,t.oper_in='U' where t.mcht_cd=? ";
		List<Object> parmaslist=new ArrayList<Object>();
		parmaslist.add(new Date());
		parmaslist.add(mcht_cd);
		sqlBaseDao.deleteBySql(updateSql, parmaslist);
	}
	/**
	 * 将更改43信息写入到新表中
	 * @throws DaoException 
	 */
	
	public void insert43Info(List<Object> querylist,List<Object> list,Operators op) throws DaoException{
		//插入表的时间
		String mcht_cd=String.valueOf(loopList(querylist).get(0));
		String name_busi=String.valueOf(loopList(querylist).get(1));
		 
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM--dd HH:mm:ss");
		String date=sdf.format(new Date());
		TblAreaInf ta = new TblAreaInf();
		ta.setMchtCd(mcht_cd);
		ta.setName_busi(name_busi);
		ta.setName_busi_his(String.valueOf(list.get(1)));
		ta.setOpId(String.valueOf(op.getId()));
		ta.setCreatedate(new Date());
		baseDao.editSave(ta);
	}
	
	
	/**
	 * 循环查询的list  得到原表中的商户号和32域(43域)
	 */
	public List<Object> loopList(List<Object> querylist){
		ArrayList<Object> arrayList = new ArrayList<Object>();
		if(!querylist.isEmpty()){
			for(Object obj:querylist){
				Object[] ob = (Object[])obj;
				arrayList.add(ob[0]);
				arrayList.add(ob[1]);
			}
			return arrayList;
		}else{
			return querylist;
		}
		
	}
	
	
	
	
	
	
	/**
	 * 将File装换成Map集合
	 * @param file
	 * @param map
	 */
	@SuppressWarnings("deprecation")
	protected boolean transfromToExcel32(File file,LinkedHashMap<Integer,List<Object>> map,LinkedHashMap<String,Object> resultMap)
	{
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(file.getPath());
			// 得到Excel工作表对象
			XSSFSheet sheet = xwb.getSheetAt(0);
			// 得到行数
			int trLength = sheet.getPhysicalNumberOfRows();
			if(trLength == 0){
				logger.info("上传文件缺少列名");
				resultMap.put("result", "上传文件缺少列名");
				return false;
			}			
			logger.info("Excel中记录的总行数是：" + (trLength - 1));			
			// 每条记录列数
			Row row = sheet.getRow(0);
						
			int tdLength = row.getPhysicalNumberOfCells();
			if(tdLength<1){
				logger.info("上传的文件缺少必需的列名");
				resultMap.put("result", "上传的文件缺少必需的列名");
				return false;
			}
			if(tdLength>2){
				logger.info("上传的文件存在多余的列名");
				resultMap.put("result", "上传的文件存在多余的列名");
				return false;
			}
			logger.info("每条记录的列数："+tdLength);
				
			//验证标题是否一致
			String reason_error=null;
			String[] title={"商户号","变更32域"};
			String titleNameMchtCd=null;
			String titleName32=null;
				row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//CELL_TYPE_STRING
				row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//CELL_TYPE_STRING
				titleNameMchtCd=row.getCell(0).getStringCellValue();
				titleName32=row.getCell(1).getStringCellValue();
				if(!titleNameMchtCd.equals(title[0]))
				{
					reason_error="第1行第1列的列名不正确,正确的列名为【"+title[0]+"】";
					logger.info(reason_error);
					resultMap.put("result", reason_error);
					return false;
				}
				if(!titleName32.equals(title[1]))
				{
					reason_error="第1行第2列的列名不正确,正确的列名为【"+title[1]+"】";
					logger.info(reason_error);
					resultMap.put("result", reason_error);
					return false;
				}
			
			if(trLength == 1){
				logger.info("上传文件内容为空");
				resultMap.put("result", "上传文件内容为空");
				return false;
			}
			if(trLength > 1001){
				logger.info("上传文件超过1000条");
				resultMap.put("result", "上传文件超过1000条");
				return false;
			}
			for (int i = 1; i < trLength; i++) {
				List<Object> tdList = new ArrayList<Object>();
				Row r = sheet.getRow(i);
				
				for (int j = 0; j < tdLength; j++) {
					Cell c = r.getCell(j);
					/*
					 * 将所有列的内容都设置成String类型，否则会出Excel异常： Cannot get a text value
					 * from a numeric cell
					 */
					if (c != null) {
						c.setCellType(Cell.CELL_TYPE_STRING);
						tdList.add(c.getStringCellValue());
					}else{
						tdList.add("");
					}					
				}
				map.put(i, tdList);
			}
		} catch (Exception e) {
			logger.error("将Excel中数据封装到List容器中异常：" + e.getMessage());
			resultMap.put("result", "将Excel中数据封装到List容器中异常："+ e.getMessage());
			return false;
		}
		return true;
	}
	/**
	 * 将File装换成Map集合
	 * @param file
	 * @param map
	 */
	@SuppressWarnings("deprecation")
	protected boolean transfromToExcel43(File file,LinkedHashMap<Integer,List<Object>> map,LinkedHashMap<String,Object> resultMap)
	{
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(file.getPath());
			// 得到Excel工作表对象
			XSSFSheet sheet = xwb.getSheetAt(0);
			// 得到行数
			int trLength = sheet.getPhysicalNumberOfRows();
			if(trLength == 0){
				logger.info("上传文件缺少列名");
				resultMap.put("result", "上传文件缺少列名");
				return false;
			}			
			logger.info("Excel中记录的总行数是：" + (trLength - 1));			
			// 每条记录列数
			Row row = sheet.getRow(0);
			
			int tdLength = row.getPhysicalNumberOfCells();
			if(tdLength<1){
				logger.info("上传的文件缺少必需的列名");
				resultMap.put("result", "上传的文件缺少必需的列名");
				return false;
			}
			if(tdLength>2){
				logger.info("上传的文件存在多余的列名");
				resultMap.put("result", "上传的文件存在多余的列名");
				return false;
			}
			logger.info("每条记录的列数："+tdLength);
			
			//验证标题是否一致
			String reason_error=null;
			String[] title={"商户号","变更43域"};
			String titleNameMchtCd=null;
			String titleName43=null;
			row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//CELL_TYPE_STRING
			row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//CELL_TYPE_STRING
			titleNameMchtCd=row.getCell(0).getStringCellValue();
			titleName43=row.getCell(1).getStringCellValue();
			if(!titleNameMchtCd.equals(title[0]))
			{
				reason_error="第1行第1列的列名不正确,正确的列名为【"+title[0]+"】";
				logger.info(reason_error);
				resultMap.put("result", reason_error);
				return false;
			}
			if(!titleName43.equals(title[1]))
			{
				reason_error="第1行第2列的列名不正确,正确的列名为【"+title[1]+"】";
				logger.info(reason_error);
				resultMap.put("result", reason_error);
				return false;
			}
			
			if(trLength == 1){
				logger.info("上传文件内容为空");
				resultMap.put("result", "上传文件内容为空");
				return false;
			}
			if(trLength > 1001){
				logger.info("上传文件超过1000条");
				resultMap.put("result", "上传文件超过1000条");
				return false;
			}
			for (int i = 1; i < trLength; i++) {
				List<Object> tdList = new ArrayList<Object>();
				Row r = sheet.getRow(i);
				
				for (int j = 0; j < tdLength; j++) {
					Cell c = r.getCell(j);
					/*
					 * 将所有列的内容都设置成String类型，否则会出Excel异常： Cannot get a text value
					 * from a numeric cell
					 */
					if (c != null) {
						c.setCellType(Cell.CELL_TYPE_STRING);
						tdList.add(c.getStringCellValue());
					}else{
						tdList.add("");
					}					
				}
				map.put(i, tdList);
			}
		} catch (Exception e) {
			logger.error("将Excel中数据封装到List容器中异常：" + e.getMessage());
			resultMap.put("result", "将Excel中数据封装到List容器中异常："+ e.getMessage());
			return false;
		}
		return true;
	}
	/**
	 * 验证数据正确性
	 * @param map
	 */
	public boolean verifyDataCorrectness32(Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		Iterator<Entry<Integer, List<Object>>> it=map.entrySet().iterator();
		String error_reason=null;
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();			
			//商户号
			String regex="\\d{1,15}";
			String num=Constance.Null2EmptyTrim(list.get(0));
			if(!Pattern.matches(regex,num))
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号不规范,只能包含数字字符且长度不能超过15，也不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			//32域
			String yu32="\\d{1,8}";
			String get32=Constance.Null2EmptyTrim(list.get(1));
			if(!Pattern.matches(yu32,get32))
			{
				error_reason="第"+(entry.getKey()+1)+"行第2列32域不规范,只能包含数字字符且长度不能超过8，也不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
		}
		return true;
	}
	public boolean verifyDataCorrectness43(Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		Iterator<Entry<Integer, List<Object>>> it=map.entrySet().iterator();
		String error_reason=null;
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();			
			//商户号
			String regex="\\d{1,15}";
			String num=Constance.Null2EmptyTrim(list.get(0));
			if(!Pattern.matches(regex,num))
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号不规范,只能包含数字字符且长度不能超过15，也不能为空！";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			//32域
			/*String yu32="\\d{1,8}";
			String get32=Constance.Null2EmptyTrim(list.get(1));
			if(!Pattern.matches(yu32,get32))
			{
				error_reason="第"+(entry.getKey()+1)+"行第2列32域不规范,只能包含数字字符且长度不能超过8，也不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}*/
			//43域
			String get43=Constance.Null2EmptyTrim(list.get(1));
			if(get43.isEmpty())
			{
				error_reason="第"+(entry.getKey()+1)+"行第2列的43域不能为空！";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
		}
		return true;
	}


}
